USE [TRACTOR_DATABASE]
GO

/****** Object:  StoredProcedure [dbo].[SpGetCustomerHistoryDetail]    Script Date: 03/20/2014 00:42:14 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SpGetCustomerHistoryDetail]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SpGetCustomerHistoryDetail]
GO

USE [TRACTOR_DATABASE]
GO

/****** Object:  StoredProcedure [dbo].[SpGetCustomerHistoryDetail]    Script Date: 03/20/2014 00:42:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROC [dbo].[SpGetCustomerHistoryDetail]--1,1
(
@VILLAGE_ID INT
)
AS
BEGIN
IF @VILLAGE_ID <>0
BEGIN
SELECT CUSTOMER_ID
       ,CUSTOMER_NAME
       ,CONTACT_NO_1 AS 'PRIMARY CONTACT'
       ,CONTACT_NO_2 AS 'SECONDARY CONTACT'
       ,CUSTOMER_ADDRESS AS [ADDRESS]
       ,CUSTOMER_EMAIL AS [EMAIL]
       ,VILLAGE_NAME AS VILLAGE
       ,''AS [SERVICE_COUPON]
       ,'' AS [SERVICE_DATE]
       ,'' AS [SERVICE_AMOUNT]
 FROM CUSTOMER C
 INNER JOIN VILLAGES V ON C.VILLAGE_ID=V.VILLAGE_ID
 WHERE  V.VILLAGE_ID=@VILLAGE_ID AND C.STATUS_ID=(SELECT MASTER_ID FROM MASTER WHERE MASTER_VALUE='ACTIVE')
 END
 ELSE
 BEGIN
SELECT CUSTOMER_ID
       ,CUSTOMER_NAME
       ,CONTACT_NO_1 AS 'PRIMARY CONTACT'
       ,CONTACT_NO_2 AS 'SECONDARY CONTACT'
       ,CUSTOMER_ADDRESS AS [ADDRESS]
       ,CUSTOMER_EMAIL AS [EMAIL]
       ,VILLAGE_NAME AS VILLAGE
       ,''AS [SERVICE_COUPON]
       ,'' AS [SERVICE_DATE]
       ,'' AS [SERVICE_AMOUNT]
 FROM CUSTOMER C
 INNER JOIN VILLAGES V ON C.VILLAGE_ID=V.VILLAGE_ID
 WHERE  C.STATUS_ID=(SELECT MASTER_ID FROM MASTER WHERE MASTER_VALUE='ACTIVE')
END
END

GO